﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ZXCompany.Models.Entity;
using DBUtil;
using System.Data;
using System.Data.SqlClient;
namespace ZXCompany.Models.Dao
{
    public class ZX_HomePageImageDao
    {
        Database db = new Database();
        private List<ZX_HomePageImage> GetListFromDT(DataTable dt)
        {
            List<ZX_HomePageImage> lzhp = new List<ZX_HomePageImage>();
            foreach (DataRow dr in dt.Rows)
            {
                ZX_HomePageImage zhpi = new ZX_HomePageImage();
                zhpi.addTime = dr["addTime"].ToString();
                zhpi.addUserID = Convert.ToInt32(dr["addUserID"]);
                zhpi.ID = Convert.ToInt32(dr["ID"]);
                zhpi.imageHref = dr["imageHref"].ToString();
                zhpi.imageUrl = dr["imageUrl"].ToString();
                zhpi.sort = Convert.ToInt32(dr["sort"]);
                zhpi.updateTime = dr["updateTime"].ToString();
                lzhp.Add(zhpi);
            }
            return lzhp;
        }
        public List<ZX_HomePageImage> Select(SqlConnection sconn)
        {
            try 
	        {
                DataTable dt = new DataTable();
		        string sql=@"SELECT [ID]
                              ,isnull([imageUrl],'') as imageUrl
                              ,isnull([imageHref],'') as [imageHref]
                              ,isnull([sort],0) as [sort]
                              ,isnull([addUserID],0) as [addUserID]
                              ,isnull([addTime],'') as [addTime]
                              ,isnull([updateTime],'') as [updateTime]
                          FROM [ZX_HomePageImage] where isdel is null";
                dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
	        }
	        catch (Exception ex)
	        {
		        throw ex;
	        }
        }


        public List<ZX_HomePageImage> SelectById(int hid, SqlConnection sconn)
        {
            try
            {
                DataTable dt = new DataTable();
                string sql = @"SELECT [ID]
                              ,isnull([imageUrl],'') as imageUrl
                              ,isnull([imageHref],'') as [imageHref]
                              ,isnull([sort],0) as [sort]
                              ,isnull([addUserID],0) as [addUserID]
                              ,isnull([addTime],'') as [addTime]
                              ,isnull([updateTime],'') as [updateTime]
                          FROM [ZX_HomePageImage]
                          where id =@id";
                SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@id",hid)
                };
                dt = db.Squery(sql, sconn, para);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public void DeleteById(int hid, SqlConnection sconn,SqlTransaction stran)
        {
            try
            {
                DataTable dt = new DataTable();
                string sql = @"update [ZX_HomePageImage] set isdel='1',updatetime=getdate() where id =@id";
                SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@id",hid)
                };
                dt = db.Squery(sql, sconn,stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Insert(ZX_HomePageImage ZxHp, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                if (ZxHp.imageHref!="" && ( ZxHp.imageHref.Length<7 || ZxHp.imageHref.Substring(0, 7) != "http://")) 
                    ZxHp.imageHref = "http://" + ZxHp.imageHref;
                DataTable dt = new DataTable();
                string sql = @"insert into [ZX_HomePageImage] (imageUrl, imageHref, sort, addUserID, addTime) 
                         values (@imageUrl,@imageHref,@sort,@addUserID,@addTime) ";
                SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@imageUrl",ZxHp.imageUrl),
                    new SqlParameter("@imageHref",ZxHp.imageHref),
                    new SqlParameter("@sort",ZxHp.sort),
                    new SqlParameter("@addUserID",ZxHp.addUserID),
                    new SqlParameter("@addTime",ZxHp.addTime)
                };
                db.Udaquery(sql, sconn, stran, para);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public void Update(ZX_HomePageImage ZxHp, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                DataTable dt = new DataTable();
                string sql = "update [ZX_HomePageImage] set ";
                if (ZxHp.imageUrl!="")
	            {
                    sql += " imageUrl=@imageUrl,";
	            }
                sql += @"imageHref=@imageHref, 
                        sort=@sort, 
                        addUserID=@addUserID, 
                        updatetime=@updatetime where id=@id";
                if (ZxHp.imageHref!=null && ZxHp.imageHref.Length>=7 && 
                    ZxHp.imageHref.Substring(0, 7) != "http://") 
                    ZxHp.imageHref = "http://" + ZxHp.imageHref;
                SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@id",ZxHp.ID),
                    new SqlParameter("@imageUrl",ZxHp.imageUrl),
                    new SqlParameter("@imageHref",ZxHp.imageHref),
                    new SqlParameter("@sort",ZxHp.sort),
                    new SqlParameter("@addUserID",ZxHp.addUserID),
                    new SqlParameter("@updatetime",ZxHp.updateTime)
                };
                db.Udaquery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
